using System.IO;
using System.Reflection;
using GrapeCity.Documents.Excel;
using System;
using System.Data;

namespace GrapeCity.Documents.Excel.Examples.Templates.Pagination.ImageShapePagination
{
    class Program
    {
        static void Main(string[] args)
        {
			//create a new workbook
			var workbook = new GrapeCity.Documents.Excel.Workbook();
			
			// Load template file from resource
			var templateFile = GetResourceStream("xlsx\\Template_BloodTest_Logo.xlsx");
			workbook.Open(templateFile);
			
			#region Init Data
			DataTable data = new DataTable();
			data.Columns.Add("PID", typeof(string));
			data.Columns.Add("PatientName", typeof(string));
			data.Columns.Add("Age", typeof(int));
			data.Columns.Add("CollectedOn", typeof(string));
			data.Columns.Add("ReveivedOn", typeof(string));
			data.Columns.Add("CompletedOn", typeof(string));
			data.Columns.Add("Physician", typeof(string));
			data.Columns.Add("PhoneNumber", typeof(string));
			data.Columns.Add("BloodGroup", typeof(string));
			data.Columns.Add("Gender", typeof(string));
			data.Columns.Add("Category", typeof(string));
			data.Columns.Add("Test", typeof(string));
			data.Columns.Add("Result", typeof(int));
			data.Columns.Add("RangeReference", typeof(string));
			data.Columns.Add("Units", typeof(string));
			
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Albumin, Serum", 2.66, "3.2-4.6", "g/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Alkaline Phosphatase, Serum", 97, "40-150", "U/L");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Bilirubin Direct, Serum", 1.38, "0-0.5", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Bilirubin Total, Serum", 2.39, "0.2-1.2", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "GGTP (Gamma GT)", 168, "12.0-64", "U/L");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Proteins, Serum", 5.9, "6.2-8.1", "g/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "SGOT/AST", 59, "5-34", "U/L");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "SGPT/ALT", 39, "0-55", "U/L");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Bilirubin- Indirect, Serum", 0.56, "0-0.8", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "Globulin", 3.2, "1.8-3.6", "g/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "A/G Ratio", 0.8, "Ratio", "");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Liver Function Test", "SGOT/SGPT Ratio", 1.5, "Ratio", "");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "CBC", "RBC (red blood cells)", 4.4, "4.2–6.1", "µL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "CBC", "Hgb (hemoglobin)", 9, "12–18", "µL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "CBC", "WBC (white blood cells)", 6.2, "5–10", "µL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "CBC", "Hct (hematocrit)", 28, "37–52", "%L");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "CBC", "Plt (platelets)", 178, "150–450", "µL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Lipid Profile", "LDL Cholesterol", 36, "<100 ", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Lipid Profile", "HDL Cholesterol", 127, "40-60", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Lipid Profile", "Triglycerides", 236, "<150", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Lipid Profile", "VLDL", 25, "<30", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Lipid Profile", "Cholesterol, Total", 210, "100-199", "mg/dL");
			data.Rows.Add("P001", "Mark Smith", 32, "20-10-2022", "20-10-2022", "21-10-2022", "Dr. Rachel Benz", "(111)-456-2354", "B+", "Male", "Lipid Profile", "T. Chol/HDL Ratio", 5.8, "0.0-5.0", "Ratio");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "BUN Urea Nitrogen, Serum", 14, "7-25", "mg/dL");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Calcium Total, Serum", 9.0, "8.6-10.4", "mg/dL");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Creatinine, Serum", 1.09, "0.7-1.4", "mg/dL");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Phosphorus-Inorganic, Serum", 4.0, "2.1-4.3", "mg/dL");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Sodium, Serum", 145, "135-145", "mEq/L");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Urea, Serum", 19, "12-20", "mgN/dL");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Uric Acid, Serum", 5.2, "2.5-7.0", "mg/dL");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "BUN/Creatinine Ratio", 12.8, "Ratio", "");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "Kidney Function Test", "Urea/Creatinine Ratio", 17.4, "Ratio", "");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "HbA1c", "Glycated Hemoglobin (HbA1c)", 21.3, "4.0-5.7", "%");
			data.Rows.Add("P002", "Jazelle", 45, "25-10-2022", "25-10-2022", "26-10-2022", "Dr. Mark Litt", "(234)-111-4561", "AB+", "Female", "HbA1c", "Average blood glucose", 564, "<140", "mg/dL");
			#endregion
			
			// Set PaginationMode is true.
			workbook.Names.Add("TemplateOptions.PaginationMode", "true");
			
			// Add data source
			workbook.AddDataSource("ds", data);
			
			// Invoke to process the template
			workbook.ProcessTemplate();
			        
			// Save to an excel file
			workbook.Save("ImageShapePagination.xlsx");

        }

		static Stream GetResourceStream(string resourcePath)
        {
            string resource = "ImageShapePagination.Resource." + resourcePath.Replace("\\", ".");
            var assembly = typeof(Program).GetTypeInfo().Assembly;
            return assembly.GetManifestResourceStream(resource);
        }

    }
}